import hashlib
import time

import pymysql


class Database:
    def __init__(self, host='localhost', port=3306, user='root',
                 password='123456', database='dict', charset='utf8'):
        self.cur = None
        self.db = None
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        # 链接数据库
        self.connect_db()

    def connect_db(self):
        """
            创建数据库连接
        :return: 数据库连接对象
        """
        self.db = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                  database=self.database, charset=self.charset)

    def create_cursor(self):
        """
            创建游标
        :return: 游标对象
        """
        self.cur = self.db.cursor()

    def close(self):
        """
            关闭数据库
        :return:
        """
        self.cur.close()
        self.db.close()

    def register(self, name, passwd):
        """
            处理账号注册
        :param name: 用户名
        :param passwd: 密码
        :return: 是否成功True/False
        """
        sql = "select * from user where name= %s "
        self.cur.execute(sql, [name])
        # 查询到结果
        r = self.cur.fetchone()
        if r:
            print('账号已被占用')
            return False

        # 加密处理
        hash = hashlib.md5((name + "the-salt").encode())
        hash.update(passwd.encode())
        sql = "insert into user (name,passwd) values (%s,%s)"
        try:
            self.cur.execute(sql, [name, hash.hexdigest()])
            self.db.commit()
            return True
        except Exception:
            self.db.rollback()
            return False

    def login(self, name, passwd):
        """
        处理登录
        :param name: 用户名
        :param passwd: 密码
        :return: 是否登录成功
        """
        sql = "select * from user where name = %s and passwd = %s "
        hash = hashlib.md5((name + "the-salt").encode())
        hash.update(passwd.encode())
        self.cur.execute(sql, [name, hash.hexdigest()])
        r = self.cur.fetchone()
        if r:
            return True
        else:
            return False

    def insert_history(self, name, word):
        """
            插入历史记录
        :param name: 用户名
        :param word: 单词
        :return: void
        """
        tm = time.ctime()
        sql = "insert into hist(name,word,time) values (%s,%s,%s)"

        try:
            self.cur.execute(sql, [name, word, tm])
            self.db.commit()
        except Exception:
            self.db.rollback()

    def query(self, word):
        """
            单词查询
        :param word: 单词
        :return: 单词语义
        """
        sql = "select mean from words where word = %s"
        self.cur.execute(sql, [word])
        r = self.cur.fetchone()
        if r:
            return r[0]

    def history(self, name):
        """
        历史记录
        :param name: 用户名
        :return: 用户查询历史记录列表
        """
        sql = "select name,word,time from hist " \
              "where name = %s order by id desc limit 10"

        self.cur.execute(sql, [name])
        return self.cur.fetchall()
